package nl.saxion.act.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;

import nl.saxion.act.db.DatabaseManager;
import nl.saxion.act.wt.Bid;

import org.apache.tomcat.util.buf.TimeStamp;


public class BiddingDAO {

	private String sqlGetBiddingByAuction   = "SELECT * FROM Bid WHERE auctionID = ? ORDER BY bidTime DESC" ;
	private String sqlGetBiddingByPerson   = "SELECT * FROM Bid WHERE bidderID = ? ORDER BY bidTime DESC" ;
	private String sqlInsertBid = "INSERT INTO Bid (bidderID, bidPrice, bidTime, auctionID) VALUES(?, ?, ?, ?)";
	private String count		= "SELECT Count(*) FROM Bid";
	
	private Connection        con      = null ;
	private PreparedStatement psBidAuction   = null ;
	private PreparedStatement psBidPerson   = null ;
	private PreparedStatement psInsertBid = null;
	private PreparedStatement psCount 	= null;

	private int countBiddings          = 0;
	
	public BiddingDAO(DatabaseManager myDb){

		con = myDb.getConnection();
		try{
			
			this.psBidAuction   = con.prepareStatement(sqlGetBiddingByAuction) ;
			this.psBidPerson	= con.prepareStatement(sqlGetBiddingByPerson);
			this.psInsertBid    = con.prepareStatement(sqlInsertBid);
			this.psCount		= con.prepareStatement(count);
			
		    Statement stmt     = con.createStatement() ;
			ResultSet rs       = stmt.executeQuery("SELECT count(*) FROM Bid") ;
			
			if (rs.next()) {
				countBiddings = rs.getInt(1) ;
			}
			
			rs.close() ;
			stmt.close() ;
			
		} catch(SQLException se) {
			printSQLException(se) ;
		}
	}
	
	public ArrayList<Bid> getBidByPerson(int findbidderID){
		ArrayList<Bid> biddings = new ArrayList<Bid>();

		try{
			psBidPerson.clearParameters();
			
			psBidPerson.setInt(1, findbidderID);
			
			ResultSet rs = psBidAuction.executeQuery();
			
			while (rs.next()) {
				int bidderID      		= rs.getInt		("bidderID");
				double price      		= rs.getDouble  ("bidPrice");
				int bidID       		= rs.getInt		("bidID");
				int auctionID      		= rs.getInt		("auctionID");
				Bid bid = new Bid(bidderID, price, bidID, auctionID);
				
				biddings.add(bid);
			}
			
			rs.close() ;
			
		} catch(SQLException se) {
			printSQLException(se) ;
		}

		return biddings;
	}	
		
	
	public ArrayList<Bid> getBidFromAuctions(int findAuctionID){
		ArrayList<Bid> biddings = new ArrayList<Bid>();

		try{
			psBidAuction.clearParameters();
			
			psBidAuction.setInt(1, findAuctionID);
			
			ResultSet rs = psBidAuction.executeQuery();
			
			while (rs.next()) {
				int bidderID      		= rs.getInt		("bidderID");
				double price      		= rs.getDouble  ("bidPrice");
				int bidID       		= rs.getInt		("bidID");
				int auctionID      		= rs.getInt		("auctionID");
				Bid bid = new Bid(bidderID, price, bidID, auctionID);
				
				biddings.add(bid);
			}
			
			rs.close() ;
			
		} catch(SQLException se) {
			printSQLException(se) ;
		}

		return biddings;
	}	
	
	public void insertBid(int bidderID, double price, Timestamp bidTime, int auctionID){
		try {
			this.psInsertBid = con.prepareStatement(sqlInsertBid);
			
			psInsertBid.clearParameters();

			psInsertBid.setInt(1, bidderID);
			psInsertBid.setDouble(2, price);
			psInsertBid.setTimestamp(3, bidTime);
			psInsertBid.setInt(4, auctionID);
			
			psInsertBid.executeUpdate();
			
			ResultSet rs = psCount.executeQuery();
			while(rs.next()){
				countBiddings = rs.getInt(1);
			}
			
		} catch (SQLException se) {
			printSQLException(se);
			se.printStackTrace();
		}
	}

	private void printSQLException(SQLException se) {
//		while(se != null) {
//
//			System.out.print("SQLException: State:   " + se.getSQLState());
//			System.out.println("Severity: " + se.getErrorCode());
//			System.out.println(se.getMessage());			
//
//			se = se.getNextException();
//		}
		se.printStackTrace();
	}

}
